package com.hzqy.service.system;

import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.hzqy.commons.utils.*;
import com.hzqy.persistence.dao.pms.PmsDao;
import com.hzqy.web.vo.*;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.hzqy.commons.utils.ExcelUtil.MyEntry;
import com.hzqy.persistence.dao.content.ContentDao;
import com.hzqy.persistence.dao.itf.HotelItfDao;
import com.hzqy.persistence.dao.system.SysUtilDao;
import com.hzqy.service.content.ContentServiceBean;
import com.hzqy.service.core.GenericServiceBean;
import com.hzqy.service.pms.PmsServiceBean;
import com.hzqy.web.vo.stat.StatOffITVUserVo;
import com.hzqy.web.vo.system.SysConfigVo;

import static com.hzqy.commons.utils.FileUtil.fileCheck;

@Service
public class SysUtilServiceBeen extends GenericServiceBean<SysConfigVo, SysUtilDao, Integer>{
	private static final long serialVersionUID = 1L;
	protected final Logger LOG = LoggerFactory.getLogger(getClass());
	@Resource
	private SysUtilDao sysUtilDao;
	@Resource
	private PmsServiceBean pmsServiceBean;
	@Resource
	private ContentDao contentDao;
	@Resource
	private HotelItfDao hotelItfDao;
	@Resource
	private PmsDao pmsDao;

	@Override
	public SysUtilDao getEntityDao(){
		return sysUtilDao;
	}

	@Resource
	private ContentServiceBean contentServiceBean;

	/**
	 * 获取修改时间
	 *
	 */
	public String mdTimeService(String filePath,String mdTime){

		String finishPath = filePath+mdTime;
		File f = new File(finishPath);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		Calendar cal = Calendar.getInstance();
		cal.setTimeInMillis(f.lastModified());
		String time = sdf.format(cal.getTime());

		return time;
	}





	public String createExcelForModelService(){
		PmsOrgVo pmsOrgVo = null;
		ArrayList<PmsOrgVo>pmsList = new ArrayList<PmsOrgVo>();
		StringBuffer result = new StringBuffer();
		StringBuffer str = new StringBuffer();
		//获取文件流
		String filePath = SystemConfigUtils.getSysConfig("sysutil_downFile");
		String uploadPath = filePath+"组织对应的模板.xlsx";
		String finishPath = filePath+"组织对应的模板数据.xlsx";

		try {
			InputStream is = new FileInputStream(uploadPath);
			LOG.debug("解析excel文件");
			List<Map<String, Object>> list = ExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE,
					new String[] {});

			for (Map<String, Object> map : list) {
				try {
					for (String key : map.keySet()) {
						pmsOrgVo = new PmsOrgVo();
						pmsOrgVo.setF_po_name((String) map.get("用户名称"));
						pmsOrgVo.setF_po_english((String) map.get("所在分组"));
						List<PmsOrgVo> models =  sysUtilDao.selectOrgByOrgName((String) map.get("用户名称"));
						if (models.isEmpty()||models.get(0).getF_po_model_name() == null) {
							if (map.get("所在分组").equals("ZJTELECOMBSTHotelHD")) {
								pmsOrgVo.setF_po_model_name("百视通默认模板");
							} else if (map.get("所在分组").equals("ZJTELECOMHotelHD")) {
								pmsOrgVo.setF_po_model_name("高清默认模板");
							} else if (map.get("所在分组").equals("ZJTELECOMHotel")) {
								pmsOrgVo.setF_po_model_name("标清默认模板");
							}
						} else {
							pmsOrgVo.setF_po_model_name(models.get(0).getF_po_model_name());
						}
					}
					pmsList.add(pmsOrgVo);
				} catch (Exception e) {
					LOG.debug("解析出错");
					str.append("解析出错"+e);
				}finally {
					try {
						is.close();
					} catch (IOException e) {
					}
				}

			}

			LOG.debug("开始生成excel");
			List<MyEntry<String, String>> excelHeader = new ArrayList<MyEntry<String, String>>();
			excelHeader.add(new MyEntry<String, String>("f_po_name", "用户名称"));
			excelHeader.add(new MyEntry<String, String>("f_po_english", "所在分组"));
			excelHeader.add(new MyEntry<String, String>("f_po_model_name", "模块"));

			ExcelUtil.generateExcel(pmsList, excelHeader, finishPath);
			str.append("创建成功");

		} catch (FileNotFoundException e) {
			LOG.error("文件异常：",e);
		}
		return str.toString();
	}


	public String uploadExcelService(HttpServletRequest request,String model,MultipartHttpServletRequest multipart){
		StringBuffer result = new StringBuffer();
		String filePath = SystemConfigUtils.getSysConfig("sysutil_downFile");
		CommonsMultipartFile file = (CommonsMultipartFile) multipart.getFile("uploadify");
		String path = filePath+model;
		File uploadFile = new File(path);

		try {
			LOG.debug("开始上传");
			file.transferTo(uploadFile);

			result.append("导入数据成功");
		} catch (Exception e) {
			e.printStackTrace();
			LOG.error("上传文件异常：",e);
			result.append("导入数据失败" );
		}
		return result.toString();
	}

	public String removeDuplicatesOrg(MultipartFile file){
		String downPath = SystemConfigUtils.getSysConfig("sysutil_downFile");
		String create_path = downPath+"未开户组织.xlsx";
		String str = "";
		InputStream is = null;

		try {
			is = file.getInputStream();
			List<Map<String, Object>> list = ExcelUtil.parseExcel(file.getInputStream(), ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			List<PmsOrgVo> data = new ArrayList<PmsOrgVo>();
			for(int i=0;i<list.size();i++){
				try {
					Map<String, Object> m = list.get(i);
					PmsOrgVo orgVo = ConvertBeanUtil.getBeanFromMap(m, PmsOrgVo.class, PmsOrgVo.class.getName());
					List<PmsOrgVo> orgList = sysUtilDao.selectOrgByOrgName(orgVo.getF_po_name());
					if(orgList.isEmpty()){
						data.add(orgVo);
					}
				} catch (Exception e) {
					LOG.error(e.toString());
				}
			}
			LOG.debug("开始生成excel");
			List<MyEntry<String, String>> excelHeader=new ArrayList<MyEntry<String, String>>();
			excelHeader.add(new MyEntry<String,String>("f_po_name","组织名称"));
			excelHeader.add(new MyEntry<String,String>("f_po_code","企业编号"));
			excelHeader.add(new MyEntry<String,String>("f_po_address","组织地址"));
			excelHeader.add(new MyEntry<String,String>("f_po_email","电子邮箱地址"));
			excelHeader.add(new MyEntry<String,String>("f_po_stars","星级"));
			excelHeader.add(new MyEntry<String,String>("user_count","组织账号数量"));
			excelHeader.add(new MyEntry<String,String>("f_po_english","组织英文名称"));
			ExcelUtil.generateExcel(data, excelHeader, create_path);
			str = "去重成功";
		} catch (Exception e) {
			LOG.error(e.toString());
			str = "去重失败";
		}finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
		return str;
	}

	public String batchInsertOrg(MultipartFile file){
		StringBuffer str = new StringBuffer();
		int successNum = 0;//成功的数量
		InputStream is=null;
		try {
			is=file.getInputStream();
			LOG.debug("批量导入org开始");
			List<Map<String, Object>> list = ExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("解析excel完成,总数:"+list.size()+" 开始循环入库");
			List<String> orgList = sysUtilDao.selectOrgNameList(4);
			LOG.debug("平台现有组织数量："+orgList.size());

			if(list==null||list.size()<=0){
				str.append("文件解析失败或者为空！");
			}else {
				for (int i = 0; i < list.size(); i++) {
					Map<String, Object> m = list.get(i);
					PmsOrgVo orgVo = ConvertBeanUtil.getBeanFromMap(m, PmsOrgVo.class, PmsOrgVo.class.getName());
					if(StringUtils.isEmpty(orgVo.getF_po_name())){
						continue;
					}
					if (orgList.contains(orgVo.getF_po_name())) {
						LOG.debug("批量新增组织重复：" + orgVo.getF_po_name());
						continue;
					} else {
						orgVo.setF_po_code("");
						orgVo.setF_po_parentId(4);
						if(orgVo.getF_po_model()==0){
							orgVo.setF_po_model(435214);
							orgVo.setF_po_model_name("付费精选模板");
						}
						if(orgVo.getF_po_category()==null||orgVo.getF_po_category().isEmpty()){
							orgVo.setF_po_category("e");
						}
						orgVo.setF_po_address("11-0-0");
						orgVo.setF_po_join_divide(2);
						orgVo.setF_po_weatherType(1);
						orgVo.setF_po_pb_model(1);
						orgVo.setF_po_advertise_switch(1);
						orgVo.setF_po_alert(1);
						orgVo.setF_po_show_region("");
						orgVo.setF_po_status(1);
						String f_po_address_detail = "WaitingForReptile";
						orgVo.setF_po_address_detail(f_po_address_detail);
						pmsDao.insertOrg(orgVo);
						successNum++;
					}
				}
			}
			str.append("批量导入组织完成,入库条数："+successNum);
			LOG.debug(str+"");
		} catch (Exception e) {
			LOG.error("", e);
		//	str.append("批量导入失败: " + e.getMessage());
		}finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}

		return str.toString();
	}


	public String batchInsertContent(MultipartFile file,int f_catalog_id,HttpSession session){
		int successCount = 0;
		DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		StringBuffer str = new StringBuffer();
		StringBuffer str2 = new StringBuffer();
		InputStream is = null;
		try {
			LOG.debug("批量导入content开始");
			is = file.getInputStream();
			List<Map<String, Object>> list = LargeExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{"内容名称"});
			LOG.debug("解析excel完成,总数："+list.size()+", 开始循环入库");

			List<String> accountList = sysUtilDao.selectAccountList(100);
			LOG.debug("平台现有账号数量："+accountList.size());
			//10年后
			String offlineDate = "2099-12-31 23:59:59";
			String onlineDate = format.format(new Date());
			String orgName="";
			int orgId = 0;
			for(int i=0;i<list.size();i++) {
				Map<String, Object> m = list.get(i);
				CmContentVo content = ConvertBeanUtil.getBeanFromMap(m, CmContentVo.class, CmContentVo.class.getName());
				if(StringUtils.isEmpty(content.getF_cc_name())){
					continue;
				}
				if (accountList.contains(content.getF_cc_name())) {//重复
					LOG.debug("批量导入，帐号重复: " + content.getF_cc_name());
					continue;
				} else {
					if(content.getF_cc_category()==null||content.getF_cc_category().isEmpty()){
						content.setF_cc_category("0");
					}
					content.setF_cc_isfree(0);
					content.setF_cc_status(1);
					content.setF_cc_onlinedate(onlineDate);
					content.setF_cc_offlinedate(offlineDate);
					content.setF_cc_type(100);
					content.setF_cc_seriescount(1);
					content.setF_cc_programatype("1");
					content.setF_cc_interfaceType(1);
					content.setF_cc_end(1);
					if (orgName.equals(content.getF_cc_companyname())) {//上次已经查询出orgId了
						content.setF_cc_companyid(orgId + "");
					} else {
						List<PmsOrgVo> orgList = sysUtilDao.selectOrgByOrgName(content.getF_cc_companyname());
						if (!orgList.isEmpty() && orgList.size() > 0) {
							orgName = content.getF_cc_companyname();
							orgId = orgList.get(0).getF_po_id();
							content.setF_cc_companyid(orgId + "");
						} else {
							continue;
						}
					}
					contentDao.insertContent(content);
					int f_cc_id = content.getF_cc_id();
					contentDao.insertContentOrg(f_cc_id, orgId + "");
					CmCatalogContentVo cmCatalogContentVo = new CmCatalogContentVo();
					cmCatalogContentVo.setF_ccc_catalogid(f_catalog_id);
					cmCatalogContentVo.setF_ccc_contentid(f_cc_id);
					contentDao.insertBind(cmCatalogContentVo);
					successCount++;
				}
			}
			str.append("批量导入账号完成,入库条数："+successCount);
			LOG.debug(str+"");
		} catch (Exception e) {
			LOG.error("", e);
		//	str.append("批量导入失败: " + e.getMessage());
		}finally {
			try{
				if(is!=null)
					is.close();
			}catch(IOException e){
				LOG.error("zf对象IO关闭失败",e);
			}
		}
		return str.toString();
	}


	public Map<String, Object> uploadFile(MultipartFile uploadify, String filePath){
		Map<String, Object> resultMap = new HashMap<String, Object>();
		CommonsMultipartFile file = (CommonsMultipartFile) uploadify;
		if(fileCheck(filePath,filePath)){
			File uploadFile = new File(filePath);
			try {
				LOG.debug("开始上传");
				file.transferTo(uploadFile);
				resultMap.put("flag", 1);
				resultMap.put("msg", "上传成功!");
				LOG.debug("开始完成");
			} catch (Exception e) {
				LOG.error("上传失败", e);
				resultMap.put("flag", -1);
				resultMap.put("msg", "上传文件失败： ");
			}
		}else {
			resultMap.put("flag", -1);
			resultMap.put("msg", "文件格式错误");
		}
		return resultMap;
	}



	/**
	 * 步骤：
	 * 		1. 加载excelA、excelB.
	 *		2. 从excelA、excelB 提取离线用户账号列表.
	 *		3. 加载excelC.
	 *		4. 从excelC和步骤2提取的离线用户列表对比获取离线用户的信息.
	 *		5. 生成excel到磁盘缓存
	 *
	 * 其中需要注意的事项：
	 * 		1. 不可同时加载3个excel，会占用大量内存空间，基本会报OutOfMemory, 读取顺序不可更换， C文件大约40M A、B基本在12M左右，如果先读取A和B，最后解析C会非常慢
	 * 		2. 提取离线用户列表后需要释放excelA和excelB的内存空间，不然加载excelC会非常吃力，甚至卡住(每个步骤都要注意内存的释放).
	 *		3. excelC和离线用户列表的对比不能使用两层for循环嵌套, 效率极低, 1个账号的对比都可能完不成，替代方式为将list转换为map，key为业务账号 value为List中的值，使用map.get(业务账号)替代
	 *		4. list转换map的时候，必须用Iterator迭代list，一边加入map 一边删除list中的值，删除非常重要，不然可能转换也会卡住.
	 *
	 */
	public Map<String, Object> generateOfflineUserExcel(String filePathPrefix) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		LargeExcelUtil.DEBUG = true;
		long t_total = System.currentTimeMillis();

		//------------ 1 ------------
		long t1 = System.currentTimeMillis();
		LOG.debug("开始读取ExcelA 和 B");
		List<Map<String, Object>> excelA = LargeExcelUtil.parseExcel(filePathPrefix + "离线用户提取工具Excel_A.xlsx", ExcelUtil.ALLOWED_EMPTY_MODE, new String[] {});
		LOG.debug("excelA读取完成 列表数量size: " + excelA.size());
		List<Map<String, Object>> excelB = LargeExcelUtil.parseExcel(filePathPrefix + "离线用户提取工具Excel_B.xlsx", ExcelUtil.ALLOWED_EMPTY_MODE, new String[] {});
		LOG.debug("excelB读取完成 列表数量size: " + excelB.size());
		LOG.debug("excelA 和 B 均读取完成， 耗时: " + (System.currentTimeMillis() - t1) / 1000 + "s");

		LOG.debug("转换excelB list -> map  开始");
		Map<String, Map<String, Object>> excelBMap = getServeridMap(excelB);
		LOG.debug("转换excelB list -> map  结束");
		excelB = null;

		resultMap.put("state", 1);
		//------------ 1 ------------


		//------------ 2 ------------
		long t2 = System.currentTimeMillis();
		LOG.debug("开始从A、B中提取离线用户列表.");
		List<String> offline_serverid_result = new ArrayList<String>();
		Iterator<Map<String, Object>> it = excelA.iterator();
		int countA = 0;
		while(it.hasNext()) {
			if(++ countA % 10000 == 0) {
				LOG.debug("开始对比离线用户 第：" + countA + "行");
			}
			Map<String, Object> ARowMap = it.next();
			String ARowServerid = String.valueOf(ARowMap.get("用户账号"));

			Map<String, Object> BRowMap = excelBMap.get(ARowServerid);
			if(BRowMap == null) {
				offline_serverid_result.add(ARowServerid);
			}
			it.remove();
		}

		LOG.debug("从A、B中提取离线用户列表完成, 一共: " + offline_serverid_result.size() + "个离线用户, 耗时: " + (System.currentTimeMillis() - t2) / 1000 + "s");
		LOG.debug("离线业务账号列表: " + offline_serverid_result);

		//让系统回收内存
		excelA = null;
		excelBMap = null;

		resultMap.put("state", 2);
		//------------ 2 ------------


		//------------ 3 ------------
		long t3 = System.currentTimeMillis();
		LOG.debug("开始读取ExcelC");
		List<Map<String, Object>> excelC = LargeExcelUtil.parseExcel(filePathPrefix + "离线用户提取工具Excel_C.xlsx", ExcelUtil.ALLOWED_EMPTY_MODE, new String[] {});
		LOG.debug("excelC读取完成 列表数量size: " + excelC.size() + "， 耗时: " + (System.currentTimeMillis() - t3) / 1000 + "s");

		LOG.debug("转换excelC list -> map  开始");
		Map<String, Map<String, Object>> excelCMap = getServeridMap(excelC);
		LOG.debug("转换excelC list -> map  结束");
		excelC = null;

		resultMap.put("state", 3);
		//------------ 3 ------------


		//------------ 4 ------------
		long t4 = System.currentTimeMillis();
		LOG.debug("开始对比excelC, 获取离线用户所在分组信息.");
		List<Map<String, Object>> offline_user_excel_list = new ArrayList<Map<String,Object>>(offline_serverid_result.size());

		for(int i = 0; i < offline_serverid_result.size(); i ++) {
			String offline_user = offline_serverid_result.get(i);
			Map<String, Object> CRowMap = excelCMap.get(offline_user);
			if(CRowMap != null) {
				offline_user_excel_list.add(CRowMap);
			} else {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("用户账号", offline_user);
				offline_user_excel_list.add(map);
			}
		}

		LOG.debug("对比excelC, 获取离线用户所在分组信息完成, 一共: " + offline_user_excel_list.size() + "个离线用户信息, 耗时: " + (System.currentTimeMillis() - t4) / 1000 + "s");
		LOG.debug("离线业务账号列表包括分组信息: " + offline_user_excel_list);

		//让系统释放内存
		excelC = null;
		offline_serverid_result = null;

		resultMap.put("state", 4);
		//------------ 4 ------------


		//------------ 5 ------------
		long t5 = System.currentTimeMillis();
		LOG.debug("开始生成excel");
		//map转ExcelExportTempVo,以便生成excel
		List<ExcelExportTempVo> exportVoList = new ArrayList<ExcelExportTempVo>(offline_user_excel_list.size());
		for(int i = 0; i < offline_user_excel_list.size(); i ++) {
			Map<String, Object> map = offline_user_excel_list.get(i);
			ExcelExportTempVo exportVo = ConvertBeanUtil.getBeanFromMap(map, ExcelExportTempVo.class, "offlineUserTypeList");
			//获取地区字段
			exportVo.setString_val4(getAreaByServerid(exportVo.getString_val3()));
			exportVoList.add(exportVo);
		}

		List<MyEntry<String, String>> excelHeader = new ArrayList<MyEntry<String, String>>();
		excelHeader.add(new MyEntry<String, String>("string_val1", "所在分组"));
		excelHeader.add(new MyEntry<String, String>("string_val2", "用户名称"));
		excelHeader.add(new MyEntry<String, String>("string_val3", "用户账号"));
		excelHeader.add(new MyEntry<String, String>("string_val4", "地区"));
		ExcelUtil.generateExcel(exportVoList, excelHeader, filePathPrefix + "离线用户提取工具Excel_导出.xlsx");
		LOG.debug("生成excel完成, 耗时: " + (System.currentTimeMillis() - t5) / 1000 + "s");

		resultMap.put("state", 5);
		//------------ 5 ------------

		LOG.debug("生成离线用户excel功能结束，总耗时: " + (System.currentTimeMillis() - t_total) / 1000 + "s");

		resultMap.put("flag", 1);
		resultMap.put("msg", "成功");

		return resultMap;
	}

	public Map<String, Object> generateOrderListExcel(String filePathPrefix) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		long t1 = System.currentTimeMillis();
		LOG.debug("开始读取ExcelC 和 D");
		List<Map<String, Object>> excelC = LargeExcelUtil.parseExcel(filePathPrefix + "离线用户提取工具Excel_C.xlsx", ExcelUtil.ALLOWED_EMPTY_MODE, new String[] {});
		LOG.debug("excelC读取完成 列表数量size: " + excelC.size());
		List<Map<String, Object>> excelD = LargeExcelUtil.parseExcel(filePathPrefix + "订购清单提取工具Excel_D.xlsx", ExcelUtil.ALLOWED_EMPTY_MODE, new String[] {});
		LOG.debug("excelD读取完成 列表数量size: " + excelD.size());
		LOG.debug("excelC 和 D 均读取完成， 耗时: " + (System.currentTimeMillis() - t1) / 1000 + "s");

		long t4 = System.currentTimeMillis();
		LOG.debug("用户清单：开始对比excelC, excelD.");
		List<Map<String, Object>> order_list = new ArrayList<Map<String,Object>>(excelD.size());
		Map<String, Map<String, Object>> excelCMap = getServeridMap(excelC);
		excelC = null;
		Iterator<Map<String, Object>> itd = excelD.iterator();
		excelD = null;
		while(itd.hasNext()) {
			Map<String, Object> DRowMap = itd.next();
			String dRowServerid = String.valueOf(DRowMap.get("ITV账号"));
			Map<String, Object> mapc = excelCMap.get(dRowServerid);
			if(mapc == null){
				order_list.add(DRowMap);
			}else{
				DRowMap.put("所在分组",mapc.get("所在分组"));
				order_list.add(DRowMap);
			}
		}
		excelCMap = null;
		LOG.debug("用户清单：比对完成，耗时: " + (System.currentTimeMillis() - t4) / 1000 + "s");

		long t5 = System.currentTimeMillis();
		LOG.debug("开始生成excel");
		//map转ExcelExportTempVo,以便生成excel
		List<ExcelExportTempVo> exportVoList = new ArrayList<ExcelExportTempVo>(order_list.size());
		for(int i = 1; i < order_list.size(); i ++) {
			Map<String, Object> map = order_list.get(i);
			ExcelExportTempVo exportVo = ConvertBeanUtil.getBeanFromMap(map, ExcelExportTempVo.class, "orderList");
			exportVoList.add(exportVo);
		}

		List<MyEntry<String, String>> excelHeader = new ArrayList<MyEntry<String, String>>();
		excelHeader.add(new MyEntry<String, String>("string_val1", "ITV账号"));
		excelHeader.add(new MyEntry<String, String>("string_val2", "所属地市"));
		excelHeader.add(new MyEntry<String, String>("string_val3", "关联商品"));
		excelHeader.add(new MyEntry<String, String>("string_val4", "商品名称"));
		excelHeader.add(new MyEntry<String, String>("string_val5", "交易金额(单位：分)"));
		excelHeader.add(new MyEntry<String, String>("string_val6", "订单状态"));
		excelHeader.add(new MyEntry<String, String>("string_val7", "关联cp"));
		excelHeader.add(new MyEntry<String, String>("string_val8", "渠道来源"));
		excelHeader.add(new MyEntry<String, String>("string_val9", "分组信息"));
		excelHeader.add(new MyEntry<String, String>("string_val10", "创建时间"));
		excelHeader.add(new MyEntry<String, String>("string_val11", "所在分组"));
		ExcelUtil.generateExcel(exportVoList, excelHeader, filePathPrefix + "订购清单提取工具Excel_导出.xlsx");
		LOG.debug("生成excel完成, 总耗时: " + (System.currentTimeMillis() - t1) / 1000 + "s");

		resultMap.put("flag", 1);
		resultMap.put("msg", "成功");
		return resultMap;
	}


	public Object generateUserListModelExcel(String filePathPrefix) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		LargeExcelUtil.DEBUG = true;
		long t_total = System.currentTimeMillis();


		long t1 = System.currentTimeMillis();
		LOG.debug("开始读取ExcelA");
		List<Map<String, Object>> excelA = LargeExcelUtil.parseExcel(filePathPrefix + "分组用户清单Excel_A.xlsx", ExcelUtil.ALLOWED_EMPTY_MODE, new String[] {});
		LOG.debug("excelA读取完成 列表数量size: " + excelA.size() + "， 耗时: " + (System.currentTimeMillis() - t1) / 1000 + "s");

		LOG.debug("查询酒店平台所有账号对应的模版 开始");
		List<ExcelExportTempVo> allUserModelList = sysUtilDao.selectAllUserModelList();
		LOG.debug("查询酒店平台所有账号对应的模版 结束");

		LOG.debug("转换excelC list -> map  开始");
		Map<String, ExcelExportTempVo> allUserModelMap = new HashMap<String, ExcelExportTempVo>(allUserModelList.size(), 1f);
		Iterator<ExcelExportTempVo> it = allUserModelList.iterator();
		while(it.hasNext()) {
			ExcelExportTempVo excelExportTempVo = it.next();
			allUserModelMap.put(excelExportTempVo.getString_val1(), excelExportTempVo);
			it.remove();
		}

		allUserModelList = null;
		LOG.debug("转换excelC list -> map  结束");

		LOG.debug("转换map -> vo 并获取账号对应模版  开始");
		Map<String, String> defaultServeridMap = getDefaultServerid();
		/*Iterator<Entry<String, String>> dsIt = defaultServeridMap.entrySet().iterator();
		while(dsIt.hasNext()) {
			Entry<String, String> entry = dsIt.next();
			ExcelExportTempVo exportVo = allUserModelMap.get(entry.getValue());
			if(exportVo != null) {
				defaultServeridMap.put(entry.getKey(), exportVo.getString_val3());
			} else {
				defaultServeridMap.put(entry.getKey(), "");
			}
		}*/
		//defaultServeridMap中存储 key:分组名 value:模版名

		List<ExcelExportTempVo> exportVoList = new ArrayList<ExcelExportTempVo>(excelA.size());
		for(int i = 0; i < excelA.size(); i ++) {
			if(i % 5000 == 0) {
				LOG.debug("正在map转vo 第：" + (i + 1) + "行.");
			}
			Map<String, Object> map = excelA.get(i);
			ExcelExportTempVo exportVo = ConvertBeanUtil.getBeanFromMap(map, ExcelExportTempVo.class, "userListTypeList");
			String model_name = "";
			ExcelExportTempVo modelVo = allUserModelMap.get(exportVo.getString_val4());
			if(modelVo != null && StringUtils.isNotEmpty(modelVo.getString_val3())) {
				model_name = modelVo.getString_val3();
			} else {
				model_name = defaultServeridMap.get(exportVo.getString_val2());
			}
			exportVo.setString_val5(model_name);
			exportVoList.add(exportVo);
		}

		excelA = null;
		LOG.debug("转换map -> vo 并获取账号对应模版  结束");


		List<LargeExcelUtil.MyEntry<String, String>> excelHeader = new ArrayList<LargeExcelUtil.MyEntry<String, String>>();
		excelHeader.add(new LargeExcelUtil.MyEntry<String, String>("string_val1", "区域"));
		excelHeader.add(new LargeExcelUtil.MyEntry<String, String>("string_val2", "所在分组"));
		excelHeader.add(new LargeExcelUtil.MyEntry<String, String>("string_val3", "用户名称"));
		excelHeader.add(new LargeExcelUtil.MyEntry<String, String>("string_val4", "用户账号"));
		excelHeader.add(new LargeExcelUtil.MyEntry<String, String>("string_val5", "模版"));
		LargeExcelUtil.generateExcel(exportVoList, excelHeader, filePathPrefix + "酒店用户清单模版Excel_导出.xlsx");
		LOG.debug("生成酒店用户清单模版excel功能结束，总耗时: " + (System.currentTimeMillis() - t_total) / 1000 + "s");

		resultMap.put("flag", 1);
		resultMap.put("msg", "成功");

		return resultMap;
	}


	private static Map<String, String> getDefaultServerid() {
		Map<String, String> map = new HashMap<String, String>();
		map.put("ZJTELECOMHotelHD", "高清默认模板");
		map.put("ZJTELECOMHotel", "标清默认模板");
		map.put("ZJTELECOMBSTHotelHD", "百视通默认模板");

		return map;
	}


	/**
	 * generateOfflineUserExcel方法的工具方法，用于list -> map
	 */
	public static Map<String, Map<String, Object>> getServeridMap(List<Map<String, Object>> list) {
		Map<String, Map<String, Object>> resultMap = new HashMap<String, Map<String,Object>>(list.size(), 1f);
		Iterator<Map<String, Object>> it = list.iterator();
		while(it.hasNext()) {//效率比for循环高非常多，需要一边加入map后，一边删除List中的数据，如果不删，几乎不可能运行成功
			Map<String, Object> map = it.next();
			resultMap.put(String.valueOf(map.get("用户账号")), map);
			it.remove();
		}
		return resultMap;
	}

	public Object generateUserExcelByGroup(String filePathPrefix) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		LargeExcelUtil.DEBUG = true;
		long t_total = System.currentTimeMillis();

		long t1 = System.currentTimeMillis();
		LOG.debug("开始读取ExcelA");
		List<Map<String, Object>> excelA = LargeExcelUtil.parseExcel(filePathPrefix + "酒店平台分组BI活跃用户清单Excel_A.xlsx", ExcelUtil.ALLOWED_EMPTY_MODE, new String[] {});
		LOG.debug("excelA读取完成 列表数量size: " + excelA.size() + "， 耗时: " + (System.currentTimeMillis() - t1) / 1000 + "s");

		LOG.debug("匹配分组 开始");
		String[] areaArr = {"杭州","宁波","温州","嘉兴","湖州","绍兴","金华","衢州","舟山","台州","丽水","总和"};
		ArrayList al = new ArrayList();
		for(int i=0;i<areaArr.length;i++){
			al.add(areaArr[i]);
		}
		int[] arr1 = new int[12];//酒店平台分组BI活跃用户数
		int[] arr2 = new int[12];//全平台BI活跃用户数
		int[] arr3 = new int[12];//ZJTELECOMG500country
		int[] arr4 = new int[12];//ZJTELECOMG500info
		int[] arr5 = new int[12];//gdgexinghua
		int flag = 1;

		List<ExcelExportTempVo> exportVoList = new ArrayList<ExcelExportTempVo>(excelA.size());
		for(int i = 0; i < excelA.size(); i ++) {
			Map<String, Object> map = excelA.get(i);
			ExcelExportTempVo exportVo = ConvertBeanUtil.getBeanFromMap(map, ExcelExportTempVo.class, "offlineUserTypeList");
			if("ZJTELECOMHotelHD".equals(exportVo.getString_val1())||"ZJTELECOMHotel".equals(exportVo.getString_val1())||"ZJTELECOMBSTHotelHD".equals(exportVo.getString_val1())){
				String area = getAreaByServerid(exportVo.getString_val3());
				if(!"未知".equals(area)&&!"测试分组".equals(area)){
					int index = al.indexOf(area);
					arr1[index] = arr1[index] + 1;
					arr2[index] = arr2[index] + 1;
				}
			}else if("酒店分组".equals(exportVo.getString_val1())||"酒店高级分组".equals(exportVo.getString_val1())||"广电酒店高清分组".equals(exportVo.getString_val1())||"广电酒店标清分组".equals(exportVo.getString_val1())){
				String area = getAreaByServerid(exportVo.getString_val3());
				if(!"未知".equals(area)&&!"测试分组".equals(area)){
					int index = al.indexOf(area);
					arr2[index] = arr2[index] + 1;
				}
			}else if("ZJTELECOMG500country".equals(exportVo.getString_val1())){
				String area = getAreaByServerid(exportVo.getString_val3());
				if(!"未知".equals(area)&&!"测试分组".equals(area)){
					int index = al.indexOf(area);
					arr3[index] = arr3[index] + 1;
				}
			}else if("ZJTELECOMG500info".equals(exportVo.getString_val1())){
				String area = getAreaByServerid(exportVo.getString_val3());
				if(!"未知".equals(area)&&!"测试分组".equals(area)){
					int index = al.indexOf(area);
					arr4[index] = arr4[index] + 1;
				}
			}else if("gdgexinghua".equals(exportVo.getString_val1())){
				String area = getAreaByServerid(exportVo.getString_val3());
				if(!"未知".equals(area)&&!"测试分组".equals(area)){
					int index = al.indexOf(area);
					arr5[index] = arr5[index] + 1;
				}
			}
		}
		arr1[11] = arr1[0]+arr1[1]+arr1[2]+arr1[3]+arr1[4]+arr1[5]+arr1[6]+arr1[7]+arr1[8]+arr1[9]+arr1[10];
		arr2[11] = arr2[0]+arr2[1]+arr2[2]+arr2[3]+arr2[4]+arr2[5]+arr2[6]+arr2[7]+arr2[8]+arr2[9]+arr2[10];
		arr3[11] = arr3[0]+arr3[1]+arr3[2]+arr3[3]+arr3[4]+arr3[5]+arr3[6]+arr3[7]+arr3[8]+arr3[9]+arr3[10];
		arr4[11] = arr4[0]+arr4[1]+arr4[2]+arr4[3]+arr4[4]+arr4[5]+arr4[6]+arr4[7]+arr4[8]+arr4[9]+arr4[10];
		arr5[11] = arr5[0]+arr5[1]+arr5[2]+arr5[3]+arr5[4]+arr5[5]+arr5[6]+arr5[7]+arr5[8]+arr5[9]+arr5[10];

		List<StatActiveUserPvUvVo> sucdNum1 = new ArrayList<StatActiveUserPvUvVo>();
		for(int i=0;i<areaArr.length;i++){
			StatActiveUserPvUvVo sucdVo = new StatActiveUserPvUvVo();
			sucdVo.setF_sdaupu_area(areaArr[i]);
			sucdVo.setF_sdaupu_pv(arr1[i]);
			sucdNum1.add(sucdVo);
		}

		List<MyEntry<String, String>> excelHeader1=new ArrayList<MyEntry<String, String>>();
		excelHeader1.add(new MyEntry<String,String>("f_sdaupu_area","地区"));
		excelHeader1.add(new MyEntry<String,String>("f_sdaupu_pv","活跃用户数"));
		boolean flag1=ExcelUtil.generateExcel(sucdNum1, excelHeader1, filePathPrefix+"酒店平台分组BI活跃用户数.xlsx");
		if(flag1){
			List<StatActiveUserPvUvVo> sucdNum2 = new ArrayList<StatActiveUserPvUvVo>();
			for(int i=0;i<areaArr.length;i++){
				StatActiveUserPvUvVo sucdVo = new StatActiveUserPvUvVo();
				sucdVo.setF_sdaupu_area(areaArr[i]);
				sucdVo.setF_sdaupu_pv(arr2[i]);
				sucdNum2.add(sucdVo);
			}

			List<MyEntry<String, String>> excelHeader2=new ArrayList<MyEntry<String, String>>();
			excelHeader2.add(new MyEntry<String,String>("f_sdaupu_area","地区"));
			excelHeader2.add(new MyEntry<String,String>("f_sdaupu_pv","活跃用户数"));
			boolean flag2=ExcelUtil.generateExcel(sucdNum2, excelHeader2, filePathPrefix+"全平台BI活跃用户数.xlsx");
			if(flag2){
				List<StatActiveUserPvUvVo> sucdNum3 = new ArrayList<StatActiveUserPvUvVo>();
				for(int i=0;i<areaArr.length;i++){
					StatActiveUserPvUvVo sucdVo = new StatActiveUserPvUvVo();
					sucdVo.setF_sdaupu_area(areaArr[i]);
					sucdVo.setF_sdaupu_pv(arr3[i]);
					sucdNum3.add(sucdVo);
				}

				List<MyEntry<String, String>> excelHeader3=new ArrayList<MyEntry<String, String>>();
				excelHeader3.add(new MyEntry<String,String>("f_sdaupu_area","地区"));
				excelHeader3.add(new MyEntry<String,String>("f_sdaupu_pv","活跃用户数"));
				boolean flag3=ExcelUtil.generateExcel(sucdNum3, excelHeader3, filePathPrefix+"ZJTELECOMG500country活跃用户数.xlsx");
				if(flag3){
					List<StatActiveUserPvUvVo> sucdNum4 = new ArrayList<StatActiveUserPvUvVo>();
					for(int i=0;i<areaArr.length;i++){
						StatActiveUserPvUvVo sucdVo = new StatActiveUserPvUvVo();
						sucdVo.setF_sdaupu_area(areaArr[i]);
						sucdVo.setF_sdaupu_pv(arr4[i]);
						sucdNum4.add(sucdVo);
					}

					List<MyEntry<String, String>> excelHeader4=new ArrayList<MyEntry<String, String>>();
					excelHeader4.add(new MyEntry<String,String>("f_sdaupu_area","地区"));
					excelHeader4.add(new MyEntry<String,String>("f_sdaupu_pv","活跃用户数"));
					boolean flag4=ExcelUtil.generateExcel(sucdNum4, excelHeader4, filePathPrefix+"ZJTELECOMG500info活跃用户数.xlsx");
					if(flag4){
						List<StatActiveUserPvUvVo> sucdNum5 = new ArrayList<StatActiveUserPvUvVo>();
						for(int i=0;i<areaArr.length;i++){
							StatActiveUserPvUvVo sucdVo = new StatActiveUserPvUvVo();
							sucdVo.setF_sdaupu_area(areaArr[i]);
							sucdVo.setF_sdaupu_pv(arr5[i]);
							sucdNum5.add(sucdVo);
						}

						List<MyEntry<String, String>> excelHeader5=new ArrayList<MyEntry<String, String>>();
						excelHeader5.add(new MyEntry<String,String>("f_sdaupu_area","地区"));
						excelHeader5.add(new MyEntry<String,String>("f_sdaupu_pv","活跃用户数"));
						boolean flag5=ExcelUtil.generateExcel(sucdNum5, excelHeader5, filePathPrefix+"gdgexinghua活跃用户数.xlsx");
						if(!flag5){
							flag = -1;
						}
					}else{
						flag = -1;
					}
				}else{
					flag = -1;
				}
			}else{
				flag = -1;
			}
		}else{
			flag = -1;
		}

		LOG.debug("匹配分组  结束 共用时"+(System.currentTimeMillis() - t_total));
		if(flag==1){
			resultMap.put("flag", 1);
			resultMap.put("msg", "成功");
			LOG.debug("匹配分组成功。");
		}else{
			resultMap.put("flag", -1);
			resultMap.put("msg", "失败");
			LOG.debug("匹配分组失败。");
		}

		return resultMap;
	}

	public String batchInsertOrder(MultipartFile file,HttpSession session){
		int successCount = 0;
		StringBuffer str = new StringBuffer();
		InputStream is = null;
		try {
			LOG.debug("批量导入订购清单开始");
			is = file.getInputStream();
			List<Map<String, Object>> list = ExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("解析excel完成, 开始删除并循环入库");
			sysUtilDao.deleteOrder();
			for(int i=0;i<list.size();i++){
				try {
					Map<String, Object> map = list.get(i);
					ExcelExportTempVo exportVo = ConvertBeanUtil.getBeanFromMap(map, ExcelExportTempVo.class, "orderList");
					int  success = sysUtilDao.insertOrder(exportVo);
					successCount += success;
				} catch (Exception e) {
					LOG.error("", e);
					//str.append("第").append(i+1).append("条 出错! ").append(e.getMessage()).append("<br/>");
				}
			}
			LOG.debug("批量完成");
			str.append("批量导入完成， 一共：").append(list.size()).append("条数据, 成功").append(successCount).append(", 失败").append(list.size() - successCount);
		} catch (Exception e) {
			LOG.error("", e);
			str.append("批量导入失败: ");
		}finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
		return str.toString();
	}

	public String batchInsertAllUser(MultipartFile file,HttpSession session){
		StringBuffer str = new StringBuffer();
		int size = 10000;
		int index = 0;
		long start = System.currentTimeMillis();
		List<ExcelExportTempVo> exportVoList = new ArrayList<ExcelExportTempVo>(10002);
		try {
			LOG.debug("批量导入全平台用户清单开始");
			List<Map<String, Object>> list = LargeExcelUtil.parseExcel(file.getInputStream(), ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("解析excel完成, 开始删除并循环入库");
			sysUtilDao.deleteUserOrg();
			LOG.debug("删除旧表数据完成");
			for(int i=0;i<list.size();i++){
				try {
					Map<String, Object> map = list.get(i);
					ExcelExportTempVo exportVo = ConvertBeanUtil.getBeanFromMap(map, ExcelExportTempVo.class, "userListTypeList");
					exportVoList.add(exportVo);
					if(i==(index+size)){
						insertUserOrg(exportVoList);
						index = index+size;
						exportVoList.clear();
					}
				} catch (Exception e) {
					LOG.error("", e);
				}
			}
			insertUserOrg(exportVoList);
			LOG.debug("用户清单批量入库全部完成！！！总用时"+(System.currentTimeMillis() - start) + "ms");
			str.append("批量导入完成， 一共：").append(list.size()).append("条数据, 成功");
		} catch (Exception e) {
			LOG.error("", e);
			str.append("批量导入失败: " );
		}
		return str.toString();
	}

	public String batchInsertAllBICPUser(MultipartFile file,HttpSession session){
		StringBuffer str = new StringBuffer();
		int size = 10000;
		int index = 0;
		long start = System.currentTimeMillis();
		List<ExcelExportTempVo> exportVoList = new ArrayList<ExcelExportTempVo>(10002);
		try {
			LOG.debug("批量导入全平台BICP用户清单开始");
			List<Map<String, Object>> list = LargeExcelUtil.parseExcel(file.getInputStream(), ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("解析excel完成, 开始删除并循环入库");
			sysUtilDao.deleteBICPUserOrg();
			LOG.debug("删除旧表数据完成");
			for(int i=0;i<list.size();i++){
				try {
					Map<String, Object> map = list.get(i);
					ExcelExportTempVo exportVo = ConvertBeanUtil.getBeanFromMap(map, ExcelExportTempVo.class, "userListTypeList");
					exportVoList.add(exportVo);
					if(i==(index+size)){
						insertBICPUserOrg(exportVoList);
						index = index+size;
						exportVoList.clear();
					}
				} catch (Exception e) {
					LOG.error("", e);
				}
			}
			insertBICPUserOrg(exportVoList);
			LOG.debug("用户清单批量入库全部完成！！！总用时"+(System.currentTimeMillis() - start) + "ms");
			str.append("批量导入完成， 一共：").append(list.size()).append("条数据, 成功");
		} catch (Exception e) {
			LOG.error("", e);
			str.append("批量导入失败: ");
		}
		return str.toString();
	}

	public void insertUserOrg(List<ExcelExportTempVo> exportVoList) {
		long insertbefore = System.currentTimeMillis();
		if(exportVoList != null && exportVoList.size() > 0) {
			sysUtilDao.insertUserOrg(exportVoList);
		}
		LOG.debug("用户清单入库完成,一共" + exportVoList.size() + "行, 耗时: " + (System.currentTimeMillis() - insertbefore) + "ms");
	}

	public void insertBICPUserOrg(List<ExcelExportTempVo> exportVoList) {
		long insertbefore = System.currentTimeMillis();
		if(exportVoList != null && exportVoList.size() > 0) {
			sysUtilDao.insertBICPUserOrg(exportVoList);
		}
		LOG.debug("用户清单入库完成,一共" + exportVoList.size() + "行, 耗时: " + (System.currentTimeMillis() - insertbefore) + "ms");
	}

	public String batchInsertHQOrder(MultipartFile file,HttpSession session){
		int successCount = 0;
		StringBuffer str = new StringBuffer();
		InputStream is=null;
		try {
			is = file.getInputStream();
			LOG.debug("批量导入环球订购清单开始");
			List<Map<String, Object>> list = ExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("解析excel完成, 开始删除并循环入库");
			sysUtilDao.deleteHQOrder();
			for(int i=0;i<list.size();i++){
				try {
					Map<String, Object> map = list.get(i);
					ExcelExportTempVo exportVo = ConvertBeanUtil.getBeanFromMap(map, ExcelExportTempVo.class, "orderList");
					int  success = sysUtilDao.insertHQOrder(exportVo);
					successCount += success;
				} catch (Exception e) {
					LOG.error("", e);
					//str.append("第").append(i+1).append("条 出错! ").append(e.getMessage()).append("<br/>");
				}
			}
			LOG.debug("批量完成");
			str.append("批量导入完成， 一共：").append(list.size()).append("条数据, 成功").append(successCount).append(", 失败").append(list.size() - successCount);
		} catch (Exception e) {
			LOG.error("", e);
			//str.append("批量导入失败: " + e.getMessage());
		}finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
		return str.toString();
	}

	/**
	 * generateOfflineUserExcel方法的工具方法，判断业务账号所在地市
	 */
	public static String getAreaByServerid(String serverid) {
		String area = "未知";
		if(serverid.matches("^((571)|(171)|(1571)|(tv571)|(tv171)|(tv1571)).*$")) {
			area = "杭州";
		} else if(serverid.matches("^((574)|(174)|(1574)|(tv574)|(tv174)|(tv1574)).*$")) {
			area = "宁波";
		} else if(serverid.matches("^((577)|(177)|(1577)|(tv577)|(tv177)|(tv1577)).*$")) {
			area = "温州";
		} else if(serverid.matches("^((573)|(173)|(1573)|(tv573)|(tv173)|(tv1573)).*$")) {
			area = "嘉兴";
		} else if(serverid.matches("^((572)|(172)|(1572)|(tv572)|(tv172)|(tv1572)).*$")) {
			area = "湖州";
		} else if(serverid.matches("^((575)|(175)|(1575)|(tv575)|(tv175)|(tv1575)).*$")) {
			area = "绍兴";
		} else if(serverid.matches("^((579)|(179)|(1579)|(tv579)|(tv179)|(tv1579)).*$")) {
			area = "金华";
		} else if(serverid.matches("^((570)|(170)|(1570)|(tv570)|(tv170)|(tv1570)).*$")) {
			area = "衢州";
		} else if(serverid.matches("^((580)|(180)|(1580)|(tv580)|(tv180)|(tv1580)).*$")) {
			area = "舟山";
		} else if(serverid.matches("^((576)|(176)|(1576)|(tv576)|(tv1576)|(tv176)).*$")) {
			area = "台州";
		} else if(serverid.matches("^((578)|(178)|(1578)|(tv578)|(tv178)|(tv1578)).*$")) {
			area = "丽水";
		} else if(serverid.matches("^((0571)).*$")) {
			area = "测试分组";
		}

		return area;
	}

	public Map<String, Object> deleteFile(String filePath){
		Map<String, Object> resultMap = new HashMap<String, Object>();
		if(fileCheck(filePath,filePath)){
			File uploadFile = new File(filePath);
			if(uploadFile.exists()){
				Boolean flag = uploadFile.delete();
				if(flag){
					resultMap.put("flag", 1);
					resultMap.put("msg", "删除成功!");
				}else{
					resultMap.put("flag", -1);
					resultMap.put("msg", "删除失败!");
				}
			}else{
				resultMap.put("flag", -1);
				resultMap.put("msg", "删除失败,文件不存在!");
			}
		}else {
			resultMap.put("flag", -1);
			resultMap.put("msg", "文件格式错误");
		}
		return resultMap;
	}

	public Map<String,Object> selectDatabaseRuntime(){
		return sysUtilDao.selectDatabaseRuntime();
	}

	public static void main(String[] args) {
		//new SysUtilServiceBeen().generateOfflineUserExcel("D:/workspace/gd_hqjc_web/gd_hqjc_admin/src/main/webapp/static/down/");
		//String hz = "^[(571)|(171)|(1571)|(tv571)|(tv171)|(tv1571)].*$";
		System.out.println(getAreaByServerid("2580000199a"));

	}

	public String batchInsertMYSKCourse(MultipartFile file, HttpSession session) {
		StringBuffer str = new StringBuffer();
		int insertCount = 0;
		InputStream is = null;
		try {
			LOG.debug("批量导入名师优课开始");
			is = file.getInputStream();
			List<Map<String, Object>> list = ExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("解析excel完成, 开始循环入库");
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> m = list.get(i);
				YxtNmsykCourseVo yxtNmsykCourseVo = ConvertBeanUtil.getBeanFromMap(m, YxtNmsykCourseVo.class, YxtNmsykCourseVo.class.getName());
				String strDate ="2099-12-12 13:11:50";//存入下线时间
				CmContentVo cmContentVo = new CmContentVo();
				cmContentVo.setF_cc_name(yxtNmsykCourseVo.getF_ync_name());
				cmContentVo.setF_cc_type(4);
				cmContentVo.setF_cc_status(1);
				cmContentVo.setF_cc_offlinedate(strDate);
				cmContentVo.setF_cc_onlinedate("2019-7-4 13:11:50");
				cmContentVo.setF_cc_programatype("1");
				contentDao.insertContent(cmContentVo);
				yxtNmsykCourseVo.setF_ync_content_id(cmContentVo.getF_cc_id());
				insertCount += sysUtilDao.insertCourse(yxtNmsykCourseVo);
			}
			str.append("导入成功;导入: " + insertCount);

		} catch (Exception e) {
			LOG.error("", e);
			//str.append("名师优课导入失败: " + e.getMessage());
		}finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}

		return str.toString();
	}





	public String batchInsertMYSKLession(MultipartFile file, HttpSession session) {
		StringBuffer str = new StringBuffer();
		int insertCount = 0;
		InputStream is = null;
		try {
			LOG.debug("批量导入名师优课开始");
			is = file.getInputStream();
			List<Map<String, Object>> list = ExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("解析excel完成, 开始循环入库");
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> m = list.get(i);
				YxtNmsykLessonVo lessonVo = ConvertBeanUtil.getBeanFromMap(m, YxtNmsykLessonVo.class, YxtNmsykLessonVo.class.getName());
				String regtime = lessonVo.getF_ynl_regtime();
				StringBuffer sb = new StringBuffer(regtime);
				sb.insert(4, "-");
				sb.insert(7, "-");
				lessonVo.setF_ynl_regtime(sb.toString());
				String strDate ="2099-12-12 13:11:50";//存入下线时间
				CmContentVo cmContentVo = new CmContentVo();
				cmContentVo.setF_cc_name(lessonVo.getF_ynl_name());
				cmContentVo.setF_cc_code(lessonVo.getF_ynl_content_code());
				cmContentVo.setF_cc_type(4);
				cmContentVo.setF_cc_status(1);
				cmContentVo.setF_cc_offlinedate(strDate);
				cmContentVo.setF_cc_onlinedate("2019-7-4 13:11:50");
				cmContentVo.setF_cc_programatype("1");
				contentDao.insertContent(cmContentVo);
				lessonVo.setF_ynl_content_id(String.valueOf(cmContentVo.getF_cc_id()));
				insertCount += sysUtilDao.insertLession(lessonVo);
			}
			str.append("导入成功;导入: " + insertCount);

		} catch (Exception e) {
			LOG.error("", e);
			//str.append("名师优课lession导入失败: " + e.getMessage());
		}finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
		return str.toString();
	}

	public String batchInsertOffITVUser(MultipartFile file, HttpSession session) {
		StringBuffer str = new StringBuffer();
		int insertCount = 0;
		InputStream is = null;
		try {
			LOG.debug("批量导入离网用户开始");
			is = file.getInputStream();
			List<Map<String, Object>> list = ExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("解析excel完成, 开始循环入库");
			sysUtilDao.deleteOffITVUser();
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> m = list.get(i);
				StatOffITVUserVo statOffITVUserVo = ConvertBeanUtil.getBeanFromMap(m, StatOffITVUserVo.class, StatOffITVUserVo.class.getName());
				if(StringUtils.isEmpty(statOffITVUserVo.getF_sou_serverid())) {
					continue;
				}
				insertCount += sysUtilDao.insertOffITVUser(statOffITVUserVo);
			}
			str.append("导入成功;导入: " + insertCount);

		} catch (Exception e) {
			LOG.error("", e);
			//str.append("离网用户导入失败: " + e.getMessage());
		}finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
		return str.toString();
	}

	public String insertMonthUnsubscribeData(MultipartFile file, HttpSession session) {
		int f_ub_type = 11;
		StringBuffer str = new StringBuffer();
		int insertCount = 0;
		DateFormat sdf = new SimpleDateFormat("yyyy-MM");
		Date date = new Date();
		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		cal.add(Calendar.MONTH, -1);
		Date lastDate = cal.getTime();
		String lastMonth = sdf.format(lastDate);
		String dbfc_productids = SystemConfigUtils.getSysConfig("dbfc_productids");
		String[] arr = dbfc_productids.split(",");
		InputStream is=null;
		try {
			is=file.getInputStream();
			List<Map<String, Object>> list = ExcelUtil.parseExcel(is, ExcelUtil.ALLOWED_EMPTY_MODE, new String[]{});
			LOG.debug("开始删除"+lastMonth+"月退订数据");
			int count = sysUtilDao.deleteUnsubscribeUserBill(lastMonth,f_ub_type);
			LOG.debug("删除完成count="+count);
			LOG.debug("解析excel完成, 开始循环入库");
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> m = list.get(i);
				UserBillVo userBillVo = ConvertBeanUtil.getBeanFromMap(m, UserBillVo.class, UserBillVo.class.getName());
				if(userBillVo.getF_ub_productid()!=0&&Arrays.asList(arr).contains(userBillVo.getF_ub_productid()+"")&&lastMonth.equals(userBillVo.getF_ub_ordertime().substring(0, 7))){
					userBillVo.setF_ub_type(f_ub_type);
					userBillVo.setF_ub_endtime("2099-12-31 23:59:59");
					userBillVo.setF_ub_unsubtime("");
					insertCount += hotelItfDao.insertUserBill(userBillVo);
				}
			}
			str.append("导入成功;导入数量: " + insertCount);
			LOG.debug("入库结束。"+str);
		} catch (Exception e) {
			LOG.error("", e);
			//str.append("月退订数据导入失败: " + e.getMessage());
		}finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
		return str.toString();
	}

	public Map<String, Object> generateMonthBusinessAnalysisExcel(String filePathPrefix) {
		DateFormat sdf = new SimpleDateFormat("yyyy-MM");
		Date date = new Date();
		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		cal.add(Calendar.MONTH, -1);
		Date lastDate = cal.getTime();
		String lastMonth = sdf.format(lastDate);

		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<ExcelExportTempVo> listAll = new ArrayList<>();
		long t1 = System.currentTimeMillis();
		LOG.debug("开始查询月经分点播时长排名数据");
		List<ExcelExportTempVo> list1 = sysUtilDao.selectPlayTimeTop30(lastMonth,0,50);
		LOG.debug("结束查询月经分点播时长排名数据，size="+list1.size()+"，用时："+(System.currentTimeMillis() - t1) / 1000 + "s");
		t1 = System.currentTimeMillis();

		LOG.debug("开始查询月经分媒资点播次数排名数据");
		List<ExcelExportTempVo> list2 = sysUtilDao.selectPlayCountTop30(lastMonth,0,50);
		LOG.debug("结束查询月经分媒资点播次数排名数据，size="+list2.size()+"，用时："+(System.currentTimeMillis() - t1) / 1000 + "s");
		t1 = System.currentTimeMillis();

		LOG.debug("开始查询月经分消费排名数据");
		List<ExcelExportTempVo> list3 = sysUtilDao.selectConsumptionTop30(lastMonth,0,50);
		LOG.debug("结束查询月经分消费排名数据，size="+list3.size()+"，用时："+(System.currentTimeMillis() - t1) / 1000 + "s");
		t1 = System.currentTimeMillis();

		if (!list1.isEmpty())
			listAll.addAll(list1);
		if (!list2.isEmpty())
			listAll.addAll(list2);
		if (!list3.isEmpty())
			listAll.addAll(list3);

		LOG.debug("开始生成excel");
		List<MyEntry<String, String>> excelHeader = new ArrayList<MyEntry<String, String>>();
		excelHeader.add(new MyEntry<String, String>("string_val1", "数据1"));
		excelHeader.add(new MyEntry<String, String>("string_val2", "数据2"));
		excelHeader.add(new MyEntry<String, String>("string_val3", "数据3"));
		ExcelUtil.generateExcel(listAll, excelHeader, filePathPrefix + "月经分排名数据Excel.xlsx");
		LOG.debug("生成excel完成, 总耗时: " + (System.currentTimeMillis() - t1) / 1000 + "s");

		resultMap.put("flag", 1);
		resultMap.put("msg", "成功");
		return resultMap;
	}

}
